﻿
var table_field = [
    {
        tablename: "sim",//sim卡表,这个表只是做登记记录,实际上关联作用不大
        fields: {
            id: { des: "序号", type: "INTEGER PRIMARY KEY AUTOINCREMENT" },
            fid: { des: "序号", type: "INTEGER" },
            mobile: { des: "手机号码", type: "TEXT" },
            deviceid: { des: "设备id", type: "TEXT" },
            org: { des: "组织机构", type: "TEXT" },
            startday: { des: "启用日期", type: "TIMESTAMP NOT NULL DEFAULT (datetime('now','localtime'))" },
            fee: { des: "套餐费用", type: "TEXT" },
            flow: { des: "每月流量", type: "TEXT" },
            yys: { des: "运营商", type: "TEXT" },
            sim: { des: "sim卡号码", type: "TEXT UNIQUE" },
            bd: { des: "是否绑定", type: "TEXT" },
            des: { des: "描述", type: "TEXT" },
            ywlx: { des: "业务类型", type: "TEXT" }
            // memo: { des: "类型", type: "TEXT" }
        },
        fieldnum: 12
    },
    {
        tablename: "log",//数据表,这个数据表是需要创建的
        fields: {
            company: { des: "客户名称", type: "TEXT" },
            deviceid: { des: "设备号码", type: "TEXT" },
            fid: { des: "父账号", type: "TEXT" },
            projectid:{des:"项目id",type:"TEXT"},
            temp: { des: "温度", type: "TEXT" },
            press: { des: "压强", type: "TEXT" },
            data: { des: "数据", type: "TEXT" },
            time: { des: "时间", type: "TIMESTAMP NOT NULL DEFAULT (datetime('now','localtime'))" }
        },
        fieldnum: 6
    },



    {
        tablename: "logfile",//日志表
        fields: {
            id: { des: "序号", type: "INTEGER PRIMARY KEY AUTOINCREMENT" },
            fid:{des:"父账号",type:"TEXT"},
            info_func: { des: "操作功能名", type: "TEXT" },
            info_name: { des: "操作名称", type: "TEXT" },
            name: { des: "操作人", type: "TEXT" },
            time: { des: "时间", type: "TIMESTAMP NOT NULL DEFAULT (datetime('now','localtime'))" },
            ip: { des: "操作ip", type: "TEXT" }
        },
        fieldnum: 4
    },
    {
        tablename: "warn_report",//报警表
        fields: {
            id: { des: "序号", type: "INTEGER PRIMARY KEY AUTOINCREMENT" },
            deviceid: { des: "设备编号", type: "TEXT" },
            time: { des: "报警时间", type: "TIMESTAMP NOT NULL DEFAULT (datetime('now','localtime'))" },
            warn_type: { des: "报警类型", type: "TEXT" },
            handle_status: { des: "处理状态", type: "INTEGER" },
            memo: { des: "处理描述", type: "TEXT" }
        },
        fieldnum: 5
    },
    {
        tablename: "warn_config",//报警设置
        fields: {
            deviceid: { des: "设备编号", type: "TEXT PRIMARY KEY" },
            warn_type: { des: "报警类型", type: "TEXT" },
            hint: { des: "是否提示", type: "TEXT" },
            hintvoice: { des: "声音提示", type: "TEXT" },
            voicefile: { des: "声音url", type: "TEXT" },
            message_warn: { des: "短信报警", type: "TEXT" },
            //warn_tele: { des: "报警电话", type: "TEXT" },
            time: { des: "报警时间", type: "TIMESTAMP NOT NULL DEFAULT (datetime('now','localtime'))" },
            account: { des: "创建账号", type: "TEXT" }
        },
        fieldnum: 9
    },
    {
        tablename: "warn_tele", //报警电话 ,根据项目而来,根据报警设置
        fields: {
            id: { des: "序号", type: "INTEGER PRIMARY KEY AUTOINCREMENT" },
            projectid: { des: "项目id", type: "INTEGER" },
            tele: { des: "电话", type: "TEXT" }
        },
        fieldnum: 3
    },
    {
        tablename: "role",//角色
        fields: {
            id: { des: "id", type: "INTEGER PRIMARY KEY" },
            name: { des: "角色名称", type: "TEXT" },
            power: { des: "权限", type: "TEXT" },
            //[{p1:A},{p2:B},{p3:C}],项目p1:权限A - 项目p2:权限B - 项目p3 权限C
            fid: { des: "创建角色的帐号", type: "TEXT" },
            use: { des: "是否启动", type: "INTEGER" }//0,1
        },
        fieldnum: 5
    },
     {
         tablename: "user",//用户
         fields: {
             id: { des: "账号", type: "TEXT PRIMARY KEY" },
             fid: { des: "父亲id", type: "TEXT" },// 属于哪个父帐号,如果是超级admin,为"0",即,没有父亲节点
             name: { des: "用户名", type: "TEXT" },
             weixin: { des: "微信号码", type: "TEXT" },
             //clientname: { des: "客户名称", type: "TEXT" },
             use: { des: "是否启动", type: "BOOLEAN" },
             timeto: { des: "到期时间", type: "TIMESTAMP" },
             pass: { des: "密码", type: "TEXT" },
             tele: { des: "电话", type: "TEXT" },
             pic: { des: "头像", type: "TEXT" },
             roleid: { des: "角色", type: "INTEGER" },
         },
         fieldnum: 11
     },
    {
        tablename: "project",
        fields: {
            id: { des: "序号", type: "INTEGER PRIMARY KEY AUTOINCREMENT" },
            fid: { des: "父亲id", type: "TEXT" },// 
            name: { des: "项目名称", type: "TEXT" },
            clientname: { des: "客户名称", type: "TEXT" },
            des: { des: "项目描述", type: "TEXT" },
            progress: { des: "项目进度", type: "TEXT" }
        }
    },
     {
         tablename: "device",//设备表
         fields: {
             id: { des: "序号", type: "INTEGER PRIMARY KEY AUTOINCREMENT" },
             fid: { des: "父亲id", type: "TEXT" },// 
             projectid: { des: "关联项目id", type: "INTEGER" },
             devicename: { des: "设备名称", type: "TEXT" },
             deviceid: { des: "设备号", type: "TEXT UNIQUE" },
             sim: { des: "sim卡号", type: "TEXT" },
             register: { des: "注册状态", type: "TEXT" },
             lockdevice: { des: "锁定设备", type: "TEXT" },
             zone: { des: "所属区域", type: "TEXT" },
             pos: { des: "地理位置", type: "TEXT" },
             cdfb: { des: "测点分布", type: "TEXT" },
             bjkg: { des: "报警开关", type: "TEXT" },
             signal: { des: "信号", type: "TEXT" },
             vol: { des: "电池电压", type: "TEXT" },
             ele_charge: { des: "电量", type: "TEXT" },
             col_cycle: { des: "采集周期", type: "TEXT" },
             warn_up: { des: "报警上限", type: "TEXT" },
             warn_down: { des: "报警下限", type: "TEXT" },
             status: { des: "状态", type: "TEXT" },
             //以下报警电话已经被淘汰,只是为了兼容
             phonenumber1: { des: "报警电话1", type: "TEXT" },
             phonenumber2: { des: "报警电话2", type: "TEXT" },
             phonenumber3: { des: "报警电话3", type: "TEXT" },
             phonenumber4: { des: "报警电话4", type: "TEXT" },
             phonenumber5: { des: "报警电话5", type: "TEXT" }
         },
         fieldnum: 23
     }


];

var table_memory = [
    {
        tablename: "log_mem",
        fields: [
            { name: "clientname", type: "TEXT", des: "设备编号" },
            { name: "deviceid", type: "TEXT", des: "设备号码" },
            { name: "pos", des: "地理位置", type: "TEXT" }
        ]
    }
];
var table_view = [
    {
        tablename: "view_warn_report",
        type: "left",
        table: {
            left: {
                alias: "a",
                name: "warn_report",
                fields: "a.id id, a.deviceid deviceid,a.time time,a.warn_type warn_type, a.handle_status handle_status,a.memo memo",
            },
            right: {
                alias: "b",
                name: "device",
                fields: "b.devicename devicename"
            }
        },
        where: "on a.deviceid = b.deviceid",
        //select a.*,b.clientname,b.devicename from (select deviceid,max(time) as time ,temp ,press from log group by deviceid) as a left join device b where a.deviceid = b.deviceid
    },
    {
        tablename: "view_log_maxtime",
        type: "group",
        sql: "select deviceid,max(time) time ,temp ,press from log group by deviceid"
    },
    {
        tablename: "view_log_now", // 即时信息
        type: "left",
        table: {
            left: {
                alias: "a",
                name: "view_log_maxtime",
                fields: "a.deviceid deviceid, a.time time, a.temp temp, a.press press",
            },
            right: {
                alias: "b",
                name: "device",
                fields: "b.devicename devicename,b.signal signal,b.vol vol,b.col_cycle col_cycle,b.ele_charge ele_charge,b.status status,b.pos pos",
            }
        },
        where: "on a.deviceid = b.deviceid",

    },
    {
        tablename: "view_warn_config",//报警信息配置
        type: "left",
        table: {
            left: {
                alias: "a",
                name: "warn_config",
                fields: "a.*",
            },
            right: {
                alias: "b",
                name: "device",
                fields: "b.clientname clientname",
            }
        },
        where: "on a.deviceid = b.deviceid",
    }

];

exports.table_field = table_field;
exports.table_view = table_view;